import os
import pandas as pd
import sqlite3

def get_data():
    # Read the Excel file
    base_dir = os.getcwd()
    file_name = 'user_id-2170858-1661000488-历史记账记录.xlsx' 
    file_path = os.path.join(base_dir, "import", file_name)
    data = pd.read_excel(file_path, engine='openpyxl')

    # Display the first few rows of the data to confirm it has been read correctly
    # print(data.head())
    return data 

def load_data_and_save_to_sqlite():
    data = get_data()
    # 创建数据库连接
    base_dir = os.getcwd()
    file_name = 'accounting.db' 
    file_path = os.path.join(base_dir, "database", file_name)
    conn = sqlite3.connect(file_path)
    # 将数据存入数据库，表名为 "records"，如果表已存在则替换
    data.to_sql('records', conn, if_exists='replace', index=False)
    # 关闭数据库连接
    conn.close()


def get_data_from_database():
    base_dir = os.getcwd()
    file_name = 'accounting.db' 
    file_path = os.path.join(base_dir, "database", file_name)
    conn = sqlite3.connect(file_path)
    query = "SELECT * FROM records"
    data = pd.read_sql(query, conn)
    conn.close()
    return data